﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.IO;

namespace CSDL_NC_07.DAO
{
    class DataProvider
    {
        public string connectionString { get; set; }
        private SqlConnection sqlConnection = null;

        public DataProvider(){
            //connectionString = @"Data Source=.\SQLEXPRESS;Initial Catalog=CSDL_NC_07;Integrated Security=True";
            //connectionString = @"Data Source=.\SQLEXPRESS;Initial Catalog=CSDL_NC_07_Index;Integrated Security=True";
            StreamReader reader = new StreamReader(@"cn.txt");

            while (!reader.EndOfStream)
            {
                string str = reader.ReadLine();
                if (!str.Contains("//"))
                {
                    connectionString = str;
                    break;
                }
            }
            reader.Close();
        }

        #region Connect()
        public void Connect() {
            try
            {
                sqlConnection = new SqlConnection(connectionString);
                sqlConnection.Open();
            }
            catch (Exception e)
            {
                throw new Exception("Không kết nối được cơ sở dữ liệu !!! " + e.Message);
            }
        }
        #endregion

        #region Disconnect()
        public void Disconnect() {
            sqlConnection.Close();
            sqlConnection = null;
        }
        #endregion

        #region bool IsConnect()
        public bool IsConnect()
        {
            return (sqlConnection != null);
        }
        #endregion

        #region SqlDataReader ExecuteQuery(string strSQL)
        public SqlDataReader ExecuteQuery(string strSQL)
        {
            SqlCommand _command = new SqlCommand(strSQL, sqlConnection);
            return _command.ExecuteReader();
        }
        #endregion

        #region object ExecuteScalar(string strSQL)
        public object ExecuteScalar(string strSQL)
        {
            SqlCommand _command = new SqlCommand(strSQL, sqlConnection);
            return _command.ExecuteScalar();
        }
        #endregion

        #region int ExecuteNoneQuery(string strSQL)
        public int ExecuteNoneQuery(string strSQL)
        {
            SqlCommand _command = new SqlCommand(strSQL, sqlConnection);
            int i = _command.ExecuteNonQuery();
            return i;
        }
        #endregion

        #region DataSet ExcuteQueryDataSet(string strSQL)
        public DataSet ExcuteQueryDataSet(string strSQL) {
            SqlDataAdapter adapter = new SqlDataAdapter(strSQL, sqlConnection);
            DataSet ds = new DataSet("DATASET");
            adapter.Fill(ds);
            return ds;
        }
        #endregion

        #region DataTable ExcuteQueryDataTable(string strSQL)
        public DataTable ExcuteQueryDataTable(string strSQL) {
            DataSet ds = ExcuteQueryDataSet(strSQL);
            return ds.Tables[0];
        }
        #endregion

        #region int ExcuteProcedureWithReturn(string procName, SqlParameter[] parameters)
        public int ExcuteProcedureWithReturn(string procName, SqlParameter[] parameters)
        {
            try
            {
                SqlCommand command = new SqlCommand();
                command.Connection = sqlConnection;
                command.CommandText = procName;
                command.CommandType = CommandType.StoredProcedure;

                if (parameters != null)
                {
                    foreach (SqlParameter para in parameters)
                    {
                        command.Parameters.Add(para);
                    }
                }

                SqlParameter returnVal = new SqlParameter("returnVal", SqlDbType.Int);
                returnVal.Direction = ParameterDirection.ReturnValue;
                command.Parameters.Add(returnVal);
                command.ExecuteNonQuery();
                return Convert.ToInt32(returnVal.Value);
            }
            catch (Exception ex)
            {
                throw new Exception("Lỗi DataService.ExcuteProcedureGetTable() \n" + ex.Message);
            }
        }
        #endregion

        #region DataTable ExcuteProcedureGetTable(string procName, SqlParameter[] parameters)
        public DataTable ExcuteProcedureGetTable(string procName, SqlParameter[] parameters)
        {
            try
            {
                SqlCommand command = new SqlCommand();
                command.Connection = sqlConnection;
                command.CommandText = procName;
                command.CommandType = CommandType.StoredProcedure;

                if (parameters != null)
                {
                    foreach (SqlParameter para in parameters)
                    {
                        command.Parameters.Add(para);
                    }
                }

                SqlDataAdapter adapter = new SqlDataAdapter(command);
                DataSet ds = new DataSet("DATASET");
                adapter.Fill(ds);
                return ds.Tables[0];
            }
            catch (Exception ex)
            {
                throw new Exception("Lỗi DataService.ExcuteProcedureGetTable() !!!\n" + ex.Message);
            }
        }
        #endregion

        #region DataTable ExcuteProcedureGetDataSet(string procName, SqlParameter[] parameters)
        public DataSet ExcuteProcedureGetDataSet(string procName, SqlParameter[] parameters)
        {
            try
            {
                SqlCommand command = new SqlCommand();
                command.Connection = sqlConnection;
                command.CommandText = procName;
                command.CommandType = CommandType.StoredProcedure;

                if (parameters != null)
                {
                    foreach (SqlParameter para in parameters)
                    {
                        command.Parameters.Add(para);
                    }
                }

                SqlDataAdapter adapter = new SqlDataAdapter(command);
                DataSet ds = new DataSet("DATASET");
                adapter.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                throw new Exception("Lỗi DataService.ExcuteProcedureGetTable() !!!\n" + ex.Message);
            }
        }
        #endregion

        #region DataSet ExcuteProcedureGetDataSetWithOutput(string procName, SqlParameter[] parameters, out decimal outputValue )
        public DataSet ExcuteProcedureGetDataSetWithOutput(string procName, SqlParameter[] parameters, out decimal outputValue)
        {
            try
            {
                SqlCommand command = new SqlCommand();
                command.Connection = sqlConnection;
                command.CommandText = procName;
                command.CommandType = CommandType.StoredProcedure;

                if (parameters != null)
                {
                    foreach (SqlParameter para in parameters)
                    {
                        command.Parameters.Add(para);
                    }
                }

                SqlDataAdapter adapter = new SqlDataAdapter(command);
                DataSet ds = new DataSet("DATASET");
                adapter.Fill(ds);

                outputValue = decimal.Parse(adapter.SelectCommand.Parameters["SoTienHoaDonXuatVien"].Value.ToString());
                return ds;
            }
            catch (Exception ex)
            {
                throw new Exception("Lỗi DataService.ExcuteProcedureGetTable() !!!\n" + ex.Message);
            }
        }
        #endregion

        #region ExcuteProcedure(string procName, SqlParameter[] parameters)
        public void ExcuteProcedure(string procName, SqlParameter[] parameters)
        {
            try
            {
                SqlCommand command = new SqlCommand();
                command.Connection = sqlConnection;
                command.CommandText = procName;
                command.CommandType = CommandType.StoredProcedure;

                if (parameters != null)
                {
                    foreach (SqlParameter para in parameters)
                    {
                        command.Parameters.Add(para);
                    }
                }

                command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception("Lỗi function DataService.ExcuteProcedure()!!! \n" + ex.Message);
            }
        }
        #endregion

    }
}
